use crate::models::DataItem;
use r2d2::Pool;
use r2d2_sqlite::SqliteConnectionManager;
use rusqlite::{params, Result};
use serde_json::value::Value;

pub struct DataItemDao {
    pub pool: Pool<SqliteConnectionManager>,
}

impl DataItemDao {
    pub fn new(pool: Pool<SqliteConnectionManager>) -> Self {
        DataItemDao { pool }
    }

    // 创建表
    pub fn create_table(&self) -> Result<(), rusqlite::Error> {
        let conn = match self.pool.get() {
            Ok(conn) => conn,
            Err(_e) => {
                return Err(rusqlite::Error::SqliteSingleThreadedMode);
            }
        };
        conn.execute(
            "CREATE TABLE IF NOT EXISTS data_item (
                id INTEGER PRIMARY KEY,
                name TEXT NOT NULL,
                value JSON,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );",
            [],
        )?;
        Ok(())
    }

    // 插入数据
    pub fn insert(&self, data_item: &DataItem) -> Result<(), rusqlite::Error> {
        let mut conn = match self.pool.get() {
            Ok(conn) => conn,
            Err(_e) => {
                return Err(rusqlite::Error::SqliteSingleThreadedMode);
            }
        };
        let tx = match conn.transaction() {
            Ok(tx) => tx,
            Err(e) => return Err(e),
        };
        tx.execute(
            "INSERT INTO data_item (name, value) VALUES (?1,?2)",
            params![data_item.name, data_item.value.to_string()],
        )?;
        tx.commit()?;
        Ok(())
    }

    // 查询所有数据
    pub fn get_all_data_items(&self) -> Result<Vec<DataItem>, rusqlite::Error> {
        let conn = match self.pool.get() {
            Ok(conn) => conn,
            Err(_e) => {
                return Err(rusqlite::Error::SqliteSingleThreadedMode);
            }
        };
        let mut stmt = match conn.prepare("SELECT * FROM data_item") {
            Ok(stmt) => stmt,
            Err(e) => return Err(e),
        };
        let rows = stmt.query_map([], |row| {
            Ok(DataItem {
                id: row.get(0)?,
                name: row.get(1)?,
                value: serde_json::from_str(&row.get::<_, String>(2)?).unwrap_or("{}".into()),
                created_at: row.get(3)?,
                updated_at: row.get(4)?,
            })
        })?;
        let mut data_items = Vec::new();
        for row in rows {
            data_items.push(row?);
        }
        Ok(data_items)
    }

    pub fn get_count_by_time_range(
        &self,
        after: &str,
        before: &str,
        name: &str,
    ) -> Result<u64, rusqlite::Error> {
        let conn = match self.pool.get() {
            Ok(conn) => conn,
            Err(_e) => {
                return Err(rusqlite::Error::SqliteSingleThreadedMode);
            }
        };
        let mut stmt = conn.prepare(
            "SELECT 
                    COUNT(*) as count 
                  FROM 
                    data_item
                  WHERE 
                    created_at BETWEEN ?1 AND ?2 AND 
                    json_extract(value, '$.w_status') = 'success' AND
                    name = ?3",
        )?;
        let count: u64 = stmt.query_row(params![before, after, name], |row| row.get(0))?;
        Ok(count)
    }
    // 根据时间范围查询数据
    pub fn get_data_items_by_time_range(
        &self,
        after: &str,
        before: &str,
        name: &str,
    ) -> Result<Vec<Value>, rusqlite::Error> {
        let conn = match self.pool.get() {
            Ok(conn) => conn,
            Err(_e) => {
                return Err(rusqlite::Error::SqliteSingleThreadedMode);
            }
        };
        let mut stmt = conn.prepare(
            "SELECT 
                    value
                  FROM 
                    data_item
                  WHERE 
                    created_at BETWEEN ?1 AND ?2 AND 
                    json_extract(value, '$.w_status') = 'success' AND
                    name = ?3",
        )?;
        let rows = stmt.query_map([before, after, name], |row| {
            Ok(serde_json::from_str(&row.get::<_, String>(0)?).unwrap_or("{}".into()))
        })?;
        let mut data_items = Vec::new();
        for row in rows {
            data_items.push(row?);
        }
        Ok(data_items)
    }
    // 根据时间范围和页码查询数据
    pub fn get_data_data_items_by_time_range_and_page(
        &self,
        after: &str,
        before: &str,
        page: i32,
        page_size: i32,
        name: &str,
    ) -> Result<Vec<Value>, rusqlite::Error> {
        // after: 2022-01-01 00:00:00
        // before: 2022-01-02 00:00:00
        let conn = match self.pool.get() {
            Ok(conn) => conn,
            Err(_e) => {
                return Err(rusqlite::Error::SqliteSingleThreadedMode);
            }
        };
        let mut stmt = conn.prepare(
            "SELECT 
                    value 
                  FROM 
                    data_item
                
                  WHERE
                    name = ?5 AND
                    created_at BETWEEN ?1 AND ?2 AND 
                    json_extract(value, '$.w_status') = 'success'
                  LIMIT ?3 OFFSET ?4
                    ",
        )?;
        let rows = stmt.query_map(
            [
                before,
                after,
                &page_size.to_string(),
                &((page - 1) * page_size).to_string(),
                name,
            ],
            |row| Ok(serde_json::from_str(&row.get::<_, String>(0)?).unwrap_or("{}".into())),
        )?;
        let mut data_items = Vec::new();
        for row in rows {
            data_items.push(row?);
        }
        Ok(data_items)
    }
}
